﻿CREATE PROCEDURE [dbo].[usp_select_Fault_Count_By_Error_Type_By_Application]
(
		@ErrorType			varchar(255) = null,
	   @StartDate			datetime         
	   ,@EndDate			datetime         
	,@application           varchar(512)=null
	   ,@Debug				bit              = 0
)
AS

/****************************************************************************
Name: usp_select_Fault_Count_By_Application.sql
*****************************************************************************/
SET NOCOUNT ON;

SET @EndDate = DATEADD(day,1,@EndDate)

-- Bootstrap
DECLARE @DebugPrefix       varchar(10);
DECLARE @ErrorCode         int;

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: ';

-- Print parameters
IF (@Debug > 0)
BEGIN
	PRINT '@StartDate = ' + CAST (@StartDate as varchar)
	PRINT '@EndDate = ' + CAST (@EndDate as varchar)
END;

--DECLARE @SQL varchar(1600)
---- Possible SQL injection issue, but since this Interal app only no worries.
--	SET @SQL ='SELECT [Application], COUNT(*) ErrorCount, [ErrorType] FROM dbo.[Fault] WHERE [DateTime] 
--BETWEEN N''' +CAST( @StartDate as varchar) +''' AND N''' + CAST(@EndDate as varchar) + ''' AND [application] in (' + @application + ')' 
--+ 'and		[ErrorType]=N''' + @ErrorType +'''

--GROUP BY [Application],[ErrorType] 
--ORDER BY [Application],[ErrorType]'

--	EXEC(@SQL)

SELECT
	COUNT(*) as 'FaultCount',
	af.[ErrorType] as 'FaultType',
	af.[Application] as 'Application'

FROM dbo.vw_AggregatedFaults af

WHERE 
		 af.[Application] = ISNULL(@Application, af.[Application])
   AND   af.[TrueDateTime] between @StartDate and @EndDate

GROUP BY af.[Application], af.[ErrorType]
ORDER BY af.[Application], af.[ErrorType]

/*
SELECT [Application], COUNT(*) ErrorCount, [ErrorType]
FROM dbo.[Fault]
WHERE [DateTime] BETWEEN @StartDate AND @EndDate
and		[ErrorType]=@ErrorType
GROUP BY [Application], [ErrorType] 
ORDER BY [Application], [ErrorType]
*/
-- CHECK FOR ERROR
SET @ErrorCode = @@Error;
IF (@ErrorCode <> 0) GOTO ERROR_HANDLER;
			
RETURN 0;

-- Handles errors.
ERROR_HANDLER:
IF (@ErrorCode <> 0)
BEGIN
    IF (@Debug > 0) 
		BEGIN
			PRINT @DebugPrefix + 'Error Code = ' + CAST(@ErrorCode AS varchar(10)) + ' occurred.';
			RETURN @ErrorCode
		END

    -- Don't return any evidence of what specific error occurred, i.e. 
    -- we want to ward off potential hackers.
    RETURN 1;
END;

SET NOCOUNT OFF;
